#!/usr/bin/python
# -*- coding: UTF-8 -*-
"""
Python 3.x
SQLAlchemy 1.4.41

SQLAlchemy 查找操作
"""
from models.user import User, Base, engine
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

"""
过滤条件
"""
# `equals`
tmp_user = session.query(User).filter(User.name == 'YiYe')
print(tmp_user)

# `not equals`
tmp_user = session.query(User).filter(User.name != 'YiYe')
print(tmp_user)

# `like`
tmp_user = session.query(User).filter(User.name.like('%YiYe%'))
print(tmp_user)

# `in`
tmp_user = session.query(User).filter(User.name.in_(['%YiYe%']))
print(tmp_user)

# 同时，in也可以作用于一个Query
tmp_user = session.query(User).filter(User.name.in_(session.query(User.name).filter(User.name.like('%YiYe%'))))
print(tmp_user)

# `not in`
tmp_user = session.query(User).filter(~User.name.in_(['%YiYe%']))
print(tmp_user)

# `is null`
tmp_user = session.query(User).filter(User.name == None)
print(tmp_user)
# 或者是
tmp_user = session.query(User).filter(User.name.is_(None))
print(tmp_user)

# `is not null`
tmp_user = session.query(User).filter(User.name != None)
print(tmp_user)
# 或者是
tmp_user = session.query(User).filter(User.name.isnot(None))
print(tmp_user)

# `and`
from sqlalchemy import and_

tmp_user = session.query(User).filter(and_(User.name == 'YiYe', User.fullname == 'YiYeBiJi'))
print(tmp_user)
# 或者是传递多个参数
tmp_user = session.query(User).filter(User.name == 'YiYe', User.fullname == 'YiYeBiJi')
print(tmp_user)
# 或者是通过多次filter操作
tmp_user = session.query(User).filter(User.name == 'YiYe').filter(User.fullname == 'YiYeBiJi')
print(tmp_user)

# `or`
from sqlalchemy import or_

tmp_user = session.query(User).filter(or_(User.name == 'YiYe', User.name == 'YiYe02'))
print(tmp_user)

"""
查找方法
"""
# `all()`
query = session.query(User).filter(User.name.like('%YiYe%')).order_by(User.id)
# 输出query的类型
print(type(query))
# > <class 'sqlalchemy.orm.query.Query'>
# 调用all方法
query = query.all()
# 输出query的类型
print(type(query))
# > <class 'list'>

# `first()`
tmp_user = session.query(User).first()
print(tmp_user)
# > <User(id='1',name='YiYe',fullname='YiYeBiJi',password='123456')>

# `one()`
# 多于一条数据
try:
    tmp_user = session.query(User).one()
    print(tmp_user)
except Exception as e:
    print(e)
# > Traceback (most recent call last):
# > ...
# > sqlalchemy.exc.MultipleResultsFound: Multiple rows were found when exactly one was required
# 少于一条数据
try:
    tmp_user = session.query(User).filter(User.id == 100).one()
    print(tmp_user)
except Exception as e:
    print(e)
# > Traceback (most recent call last):
# > ...
# > sqlalchemy.exc.NoResultFound: No row was found when one was required

# 只有一条数据
tmp_user = session.query(User).filter(User.id == 1).one()
print(tmp_user)
# > <User(id='1',name='YiYe',fullname='YiYeBiJi',password='123456')>

# `one_or_none()`
# 多于一条数据
try:
    tmp_user = session.query(User).one_or_none()
    print(tmp_user)
except Exception as e:
    print(e)
# > Traceback (most recent call last):
# > ...
# > sqlalchemy.exc.MultipleResultsFound: Multiple rows were found when exactly one was required
# 少于一条数据
tmp_user = session.query(User).filter(User.id == 100).one_or_none()
print(tmp_user)
# > None
# 只有一条数据
tmp_user = session.query(User).filter(User.id == 1).one_or_none()
print(tmp_user)
# > <User(id='1',name='YiYe',fullname='YiYeBiJi',password='123456')>

# `scalar`
tmp_user = session.query(User.name, User.fullname).filter(User.id == 1).scalar()
print(tmp_user)
# > YiYe
# 指定ORM
tmp_user = session.query(User).filter(User.id == 1).scalar()
print(tmp_user)
# > <User(id='1',name='YiYe',fullname='YiYeBiJi',password='123456')>

"""
文本SQL
"""
from sqlalchemy import text

for user in session.query(User).filter(text("id<244")).order_by(text("id")).all():
    print(user)
# > <User(id='1',name='YiYe',fullname='YiYeBiJi',password='123456')>

for user in session.query(User).filter(text("id<:value and name=:name")).params(value=224, name='YiYe').order_by(
        User.id).all():
    print(tmp_user)
# > <User(id='1',name='YiYe',fullname='YiYeBiJi',password='123456')>

for user in session.query(User).from_statement(text("select * from users where name=:name")).params(name='YiYe').all():
    print(tmp_user)
# > <User(id='1',name='YiYe',fullname='YiYeBiJi',password='123456')>

"""
计数（Count）
"""
tmp_user = session.query(User).filter(User.name.like('%YiYe%')).count()
print(tmp_user)
# > 1

from sqlalchemy import func

tmp_user = session.query(func.count(User.name), User.name).group_by(User.name).all()
print(tmp_user)
# > [(1, 'YiYe')]

tmp_user = session.query(func.count('*')).select_from(User).scalar()
print(tmp_user)
# > 1

tmp_user = session.query(func.count(User.id)).scalar()
print(tmp_user)
# > 1
